﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using VideoMonitorSystem.Model;
using System.Data;
using System.Data.SqlClient;

namespace VideoMonitorSystem.DAL
{
    /// <summary>
    /// 部门信息数据层处理类
    /// </summary>
     public class DeptInfoService
    {   
         /// <summary>
         /// 私有方法  已经SQL语句查询出所有的部门信息
         /// </summary>
         /// <param name="sql"></param>
         /// <returns></returns>
         private static List<DeptInfo> GetAllDeptInfoBySQL(string sql)
         {
             List<DeptInfo> list = new List<DeptInfo>();

             using (DataTable dataTable = DBHelper.GetDataTable(sql))
             {
                 list = GetListDeptInfoByDataTable(dataTable);
             }
             return list;
         }

         /// <summary>
         /// 私有方法 从数据行里获取数据填充到对象中
         /// </summary>
         /// <param name="row">包含具体数据的行</param>
         /// <returns>拥有数据的DeptInfo对象</returns>
         private static DeptInfo GetDeptInfoByDateRow(DataRow row)
         {
             DeptInfo deptInfo = new DeptInfo();
             deptInfo.DID = Convert.ToInt32(row["dID"].ToString());
             deptInfo.DAddress = (string)row["dAddress"].ToString();
             deptInfo.DDesc = (string)row["dDesc"].ToString();
             deptInfo.DFilePath = (string)row["dFilePath"].ToString();
             deptInfo.DVideo = (string)row["dVideo"].ToString();
             deptInfo.IsUsable = Convert.ToInt32(row["isUsable"].ToString());
             deptInfo.DInTime = Convert.ToDateTime(row["dInTime"]);
             deptInfo.DIndex = Convert.ToInt32(row["dIndex"].ToString());
             deptInfo.SystemType = (SystemType)SystemTypeService.GetSystemTypeByID(Convert.ToInt32(row["sID"]));
             return deptInfo;
         }

         /// <summary>
         /// 私有方法 将表中的数据存入集合中
         /// </summary>
         /// <param name="dataTable">被查出来的表</param>
         /// <returns>List<DeptInfo>集合</returns>
         private static List<DeptInfo> GetListDeptInfoByDataTable(DataTable dataTable)
         {
             List<DeptInfo> list = new List<DeptInfo>();
             foreach (DataRow row in dataTable.Rows)
             {
                 list.Add(GetDeptInfoByDateRow(row));
             }
             return list;
         }

         /// <summary>
         /// 查询出所有的部门信息
         /// </summary>
         /// <returns>List集合</returns>
         public static List<DeptInfo> GetAllDeptInfo()
         {
             string sql = "SELECT * FROM deptIfno";
             return GetAllDeptInfoBySQL(sql);
         }

         /// <summary>
         /// 依据isUsable字段查询出满足该条件的部门信息
         /// </summary>
         /// <param name="isUsbale">是否可用1：可用 0：不可用</param>
         /// <returns>满足该条件的List集合</returns>
         public static List<DeptInfo> GetAllDeptInfoByIsUsable(int isUsbale)
         {
             string sql = "SELECT * FROM deptInfo WHERE isUsable = @isUsable";
             List<DeptInfo> list = new List<DeptInfo>();
             using(DataTable dataTable = DBHelper.GetDataTable(sql,new SqlParameter("@isUsable",isUsbale)))
             {
                 list = GetListDeptInfoByDataTable(dataTable);
             }
             return list;
         }

         /// <summary>
         /// 获取所有视频IP地址不为空的部门信息
         /// </summary>
         /// <returns></returns>
         public static List<DeptInfo> GetDeptInfoByDVedioIsNotNull()
         {
             string sql = "SELEECT * FROM deptInfo WHERE dVedio <>  ''";
             List<DeptInfo> list = new List<DeptInfo>();
             using(DataTable dataTable = DBHelper.GetDataTable(sql))
             {
                 list = GetListDeptInfoByDataTable(dataTable);
             }
             return list;
         }

         /// <summary>
         /// 依据传入的系统类型ID和是否可用字段
         /// 查询出相对应的部门信息
         /// </summary>
         /// <param name="sID">系统类型ID</param>
         /// <param name="isUsable">是否可以 值 1 or 0</param>
         /// <returns>满足条件的部门信息集合</returns>
         public static List<DeptInfo> GetDeptInfoBySID(int sID,int isUsable)
         {
             string sql = "SELECT * FROM deptInfo WHERE isUsable = @isUsable and sID = @sID";
             List<DeptInfo> list = new List<DeptInfo>();
             SqlParameter[] par = new SqlParameter[]
             {
                new SqlParameter("@isUsable",isUsable),
                new SqlParameter("@sID",sID)
             };

             using (DataTable dataTable = DBHelper.GetDataTable(sql, par))
             {
                 list = GetListDeptInfoByDataTable(dataTable);
             }
             return list;
         }

         /// <summary>
         /// 依据指定的ID获得指定的部门信息
         /// </summary>
         /// <param name="dID">部门信息ID</param>
         /// <returns>部门信息对象</returns>
         public static DeptInfo GetDeptInfoByDID(int dID)
         {
             string sql = "SELECT * FROM deptinfo WHERE dID = @dID";
             DataRow row = DBHelper.GetDataTable(sql, new SqlParameter("@dID", dID)).Rows[0];
             return GetDeptInfoByDateRow(row);
         }
         /// <summary>
         /// 新增一个部门(监控地点)
         /// </summary>
         /// <param name="deptInfo">新增的部门对象</param>
         /// <returns>执行该SQL语句后影响的行数</returns>
         public static int AddDeptInfo(DeptInfo deptInfo)
         {
             string sql = "INSERT INTO deptInfo(dAddreass,dVedio,dDesc,dFilePath,isUsable,sID)"
                        + "values(@dAddress,@dVedio,@dDesc,@dFilePath,@isUsable,@sID)";
             SqlParameter[] par = new SqlParameter[]
             {
                new SqlParameter("@dAddress",deptInfo.DAddress),
                new SqlParameter("@dVedio",deptInfo.DVideo),
                new SqlParameter("@dDesc",deptInfo.DDesc),
                new SqlParameter("@dFilePath",deptInfo.DFilePath),
                new SqlParameter("@isUsable",deptInfo.IsUsable),
                new SqlParameter("@sID",deptInfo.SystemType.StID)
             };
             return DBHelper.GetScalar(sql,par);
         }

         /// <summary>
         /// 删除指定的部门信息
         /// </summary>
         /// <param name="dID">将被删除的部门ID</param>
         /// <returns>执行后影响的行数</returns>
         public static int DelDeptInfoByID(int dID)
         {
             string sql = "DELETEL FROM deptInfo WHERE dID = @dID";
             return DBHelper.GetScalar(sql,new SqlParameter("@dID",dID));
         }

         /// <summary>
         /// 删除指定系统类型的部门信息
         /// </summary>
         /// <param name="sID">系统类型ID</param>
         /// <returns>执行后受影响的行数</returns>
         public static int DelDeptInfosBySID(int sID)
         {
             string sql = "DELETEL FROM deptInfo WHERE sID = @sID";
             return DBHelper.GetScalar(sql,new SqlParameter("@sID",sID));
         }

         /// <summary>
         /// 更新指定的部门信息
         /// </summary>
         /// <param name="deptInfo">被更新的部门对象</param>
         /// <returns>执行后受影响的行数</returns>
         public static int ModifyDeptInfoBy(DeptInfo deptInfo)
         {
             string sql = "UPDATE deptInfo SET dAddress = @dAddress,dVedio = @dVedio, dDesc = @dDesc,dFilePath = @dFilePath,isUsable = @isUsable,"
                        + "sID = @sID WHERE dID = @dID";
             SqlParameter[] par = new SqlParameter[]
             {
                new SqlParameter("@dAddress",deptInfo.DAddress),
                new SqlParameter("@dVedio",deptInfo.DVideo),
                new SqlParameter("@dDesc",deptInfo.DDesc),
                new SqlParameter("@dFilePath",deptInfo.DFilePath),
                new SqlParameter("@isUsable",deptInfo.IsUsable),
                new SqlParameter("@sID",deptInfo.SystemType.StID),
                new SqlParameter("@dID",deptInfo.DID)
             };
             return DBHelper.GetScalar(sql,par);
         }



    }
}
